package org.gw.building.repositories;

import org.gw.building.model.CourseApply;
import org.gw.building.model.CourseInfo;
import org.gw.building.model.StuInfo;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;

import java.util.List;

public interface CourseApplyRepository extends JpaRepository<CourseApply, String> {

	@Query("select t.courseInfo.id ,count(*) from CourseApply t where t.courseInfo.id in ?1 group by t.courseInfo.id")
	List<Object[]> findCountByCourseId(List<String> ids);

	@Query("select  count(*) from CourseApply t where t.courseInfo.id = ?1")
	Long countByCourseId(String courseId);

	@Query("select t.courseInfo.id ,count(*) from CourseApply t where t.courseInfo.id in ?1 and t.stuInfo.id=?2 group by t.courseInfo.id")
	List<Object[]> findMyApplyByCourseIds(List<String> asList, String stuInfo);

	@Query("select t.courseInfo from CourseApply t where t.stuInfo.user.uid=?1 and (?2 is null or t.courseInfo.kclb=?2) and (?3 is null or t.courseInfo.name like ?3 or t.courseInfo.kcjj like ?3 or t.courseInfo.zjr like ?3)")
	Page<CourseInfo> queryMyCourseInfo(String user, String type, String fuzzyName, Pageable pageable);

	@Query("from CourseApply t where (?1 is null or t.courseInfo.id=?1) and (?2 is null or t.courseInfo.name like ?2) and (?3 is null or t.stuInfo.user.name like ?3)"
			+ " and (?4 is null or t.stuInfo.user.department=?4) and (?5 is null or t.stuInfo.user.major=?5) and (?6 is null or t.stuInfo.classInfo.id=?6)")
	Page<CourseApply> query(String course, String courseName, String stuName, String department, String major,
                            String clazz, Pageable pageable);

	@Query("from CourseApply t where (?1 is null or t.courseInfo.id=?1) and (?2 is null or t.courseInfo.name like ?2) and (?3 is null or t.stuInfo.user.name like ?3)"
			+ " and (?4 is null or t.stuInfo.user.department=?4) and (?5 is null or t.stuInfo.user.major=?5) and (?6 is null or t.stuInfo.classInfo.id=?6)")
	List<CourseApply> findCourseApplys(String course, String courseName, String stuName, String department,
                                       String major, String clazz);

	@Query("select t.stuInfo from CourseApply t where t.courseInfo.id = ?1 and (?2 is null or t.stuInfo.user.uid like ?2 or t.stuInfo.user.name like ?2) and (?3 is null or t.stuInfo.user.department=?3)"
			+ " and (?4 is null or t.stuInfo.classInfo.id=?4)")
	Page<StuInfo> queryStuInfoByCourseId(String courseId, String name, String department, String clazz,
                                         Pageable pageable);
}
